package com.sinosoft.cnmuseum.servlets;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

import com.sinosoft.cnmuseum.dao.MbGyGjDao;
import com.sinosoft.cnmuseum.dao.MbGyZjlxDao;
import com.sinosoft.cnmuseum.dao.MbGyZqDao;
import com.sinosoft.cnmuseum.dao.MbXtglJgDao;
import com.sinosoft.cnmuseum.dao.MbXtglYhxxDao;
import com.sinosoft.cnmuseum.entity.MbGyGj;
import com.sinosoft.cnmuseum.entity.MbGyZjlx;
import com.sinosoft.cnmuseum.entity.MbGyZq;
import com.sinosoft.cnmuseum.entity.MbXtglJg;
import com.sinosoft.cnmuseum.entity.MbXtglYhxx;
import com.sinosoft.cnmuseum.servlets.i.IServlet;
import com.sinosoft.cnmuseum.utils.LogUtils;
import com.sinosoft.cnmuseum.utils.MD5;
import com.sinosoft.cnmuseum.utils.MultipartFile;
import com.sinosoft.cnmuseum.utils.POIUtil;
import com.sinosoft.cnmuseum.utils.PageUtil;
import com.sinosoft.cnmuseum.utils.Status;
import com.sinosoft.cnmuseum.utils.StringUtils;
import com.sinosoft.commontools.annotated.TransAnnotation;
import com.sinosoft.commontools.annotated.rquestMethod;
import com.sinosoft.commontools.annotated.rquestMethod.ReqMode;
import com.sinosoft.commontools.record.Record;
import com.sinosoft.dbtools.operate.DBActorMgr;
import com.sinosoft.dbtools.operate.IDBActor;

/**
 * @项目名称：cnmuseum
 * @类名称： MbXtglYhxxServlet
 * @类描述：用户信息
 * @创建时间：2018年12月21日  
 * @创建人：康仕森
 */
public class MbXtglYhxxServlet implements IServlet {
	
	private static Logger logger = Logger.getLogger(MbXtglYhxxServlet.class);
	private static SimpleDateFormat dfmt=new SimpleDateFormat("yyyyMMddHHmm");
	private static List<Record> listAll = new ArrayList<Record>();
	/**
	 * 查询部门的人员信息
	 * @param request
	 * @param response
	 * @param data  所在机构szjg   姓名xm  证件类型zjlx  唯一编码wybm  性别xb  民族mz  出生日期csrq
	 * @return
	 */
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getData(HttpServletRequest request, HttpServletResponse response, Record data){
		Record result = new Record();
		try { 
			MbXtglYhxxDao dao = new MbXtglYhxxDao();
			String countsql = "SELECT count(*) as count from mb_xtgl_yhxx_view where 1=1";
		    String sql = "SELECT * from mb_xtgl_yhxx_view where 1=1";
			String limitsql = " limit ";
			String szjg = data.getStrValue(MbXtglYhxx.SZJG);
			String xm = data.getStrValue(MbXtglYhxx.XM);
			String zjlx = data.getStrValue(MbXtglYhxx.ZJLX);
			String wybm = data.getStrValue(MbXtglYhxx.WYBM);
			String xb = data.getStrValue(MbXtglYhxx.XB);
			String mz = data.getStrValue(MbXtglYhxx.MZ);
			String csrq = data.getStrValue(MbXtglYhxx.CSRQ);
			int pagenumber = data.getIntValue(Status.PAGENUMBER);// 获取当前页
			int pagesize = data.getIntValue(Status.PAGESIZE);// 获取每页多少条数据
			if(StringUtils.isNoEmpty(szjg)){
				countsql += " and "+MbXtglYhxx.SZJG+" = "+szjg+" ";
				sql += " and "+MbXtglYhxx.SZJG+" = "+szjg+" ";
			}
			if(StringUtils.isNoEmpty(xm)){
				countsql += " and "+MbXtglYhxx.XM+" like '%"+xm+"%' ";
				sql += " and "+MbXtglYhxx.XM+" like '%"+xm+"%' ";
			}
			if(StringUtils.isNoEmpty(zjlx)){
				countsql += " and "+MbXtglYhxx.ZJLX+" = "+zjlx+" ";
				sql += " and "+MbXtglYhxx.ZJLX+" = "+zjlx+" ";
			}
			if(StringUtils.isNoEmpty(wybm)){
				countsql += " and "+MbXtglYhxx.WYBM+" like '%"+wybm+"%'";
				sql += " and "+MbXtglYhxx.WYBM+" like '%"+wybm+"%'";
			}
			if(StringUtils.isNoEmpty(xb)){
				countsql += " and "+MbXtglYhxx.XB+" = "+xb+" ";
				sql += " and "+MbXtglYhxx.XB+" = "+xb+" ";
			}
			if(StringUtils.isNoEmpty(mz)){
				countsql += " and "+MbXtglYhxx.MZ+" = "+mz+" ";
				sql += " and "+MbXtglYhxx.MZ+" = "+mz+" ";
			}
			if(StringUtils.isNoEmpty(csrq)){
				countsql += " and "+MbXtglYhxx.CSRQ+" = "+csrq+"";
				sql += " and "+MbXtglYhxx.CSRQ+" = "+csrq+"";
			}
			List<Record> countlist = dao.getBySql(countsql);
			int total = countlist.get(0).getIntValue("count");
			PageUtil page = new PageUtil(pagenumber, pagesize, total);
			limitsql += page.getStart()+","+pagesize;
			listAll = dao.getBySql(sql+limitsql);
			result.add(Status.ROWS, listAll);
			result.add(Status.TOTAL, total);
			result.add(Status.PAGENUMBER, pagenumber);
			result.add(Status.PAGESIZE, pagesize);
			result.add(Status.PAGETOTAL, page.getPagetotal());
			// 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败信息
			result.add(Status.MSG,"查询失败！");
		}
	    return result;
	}
	/**
	 * 查询当前最序列号最大值
	 * @param request
	 * @param response
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getMaxSort(HttpServletRequest request, HttpServletResponse response, Record data){
		Record result = new Record();
		try { 
			MbXtglYhxxDao dao = new MbXtglYhxxDao();
		    String sql = "select max(sorted) as max from " + MbXtglYhxx.MB_XTGL_YHXX_TABLE_NAME + " where " + MbXtglYhxx.SFWZJ + "=0 ";
		    List<Record> count = dao.getBySql(sql);
		    int maxCount = count.get(0).getIntValue("max")+1;
		    result.add("max", maxCount);
		    // 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败信息
			result.add(Status.MSG,"查询失败！");
		}
	    return result;
	}
	/**
	 * 根据证件类型查询证件名称
	 * @param request
	 * @param response
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getNameByZjlxId(HttpServletRequest request, HttpServletResponse response, Record data){
		Record result = new Record();
		try { 
			MbGyZjlxDao mbGyZjlxDao = new MbGyZjlxDao();
			int zjlx = data.getIntValue(MbXtglYhxx.ZJLX);
			if(zjlx!=0){
				Record record = mbGyZjlxDao.getById(zjlx);
				result.add("name", record.getStrValue(MbGyZjlx.NAME));
				// 返回接口调用成功标识
				result.add(Status.SATUS,Status.OK);
			}
		} catch (Exception e) {
			// 返回接口调用失败信息
			result.add(Status.MSG,"查询失败！");
		}
	    return result;
	}
	/**
	 * 根据民族类型查询民族名称
	 * @param request
	 * @param response
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getNameByZqId(HttpServletRequest request, HttpServletResponse response, Record data){
		Record result = new Record();
		try { 
			MbGyZqDao mbGyZqDao = new MbGyZqDao();
			int mz = data.getIntValue(MbXtglYhxx.MZ);
			if(mz!=0){
				Record record = mbGyZqDao.getById(mz);
				result.add("name", record.getStrValue(MbGyZq.NAME));
				// 返回接口调用成功标识
				result.add(Status.SATUS,Status.OK);
			}
		} catch (Exception e) {
			// 返回接口调用失败信息
			result.add(Status.MSG,"查询失败！");
		}
	    return result;
	}
	/**
	 * 添加人员信息
	 * @param request
	 * @param response
	 * @param data 
	 * @return
	 */
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record insert(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		MD5 md5 = new MD5();
		try{
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者
            
			MbXtglYhxxDao dao = new MbXtglYhxxDao();
			Record yhxx = new Record();
			String xm = data.getStrValue(MbXtglYhxx.XM);
			String szjg = data.getStrValue(MbXtglYhxx.SZJG);
			String zjlx = data.getStrValue(MbXtglYhxx.ZJLX);
			String zjhm = data.getStrValue(MbXtglYhxx.ZJHM);
			String gj = data.getStrValue(MbXtglYhxx.GJ);
			String xb = data.getStrValue(MbXtglYhxx.XB);
			String mz = data.getStrValue(MbXtglYhxx.MZ);
			String csrq = data.getStrValue(MbXtglYhxx.CSRQ);
			String dzyj = data.getStrValue(MbXtglYhxx.DZYJ);
			String dh = data.getStrValue(MbXtglYhxx.DH);
			String sorted = data.getStrValue(MbXtglYhxx.SORTED);
			if(StringUtils.isNoEmpty(xm)){
				yhxx.add(MbXtglYhxx.XM, xm);
			}
			if(StringUtils.isNoEmpty(szjg)){
				yhxx.add(MbXtglYhxx.SZJG, szjg);
			}
			if(StringUtils.isNoEmpty(zjlx)){
				yhxx.add(MbXtglYhxx.ZJLX, zjlx);
			}
			if(StringUtils.isNoEmpty(zjhm)){
				yhxx.add(MbXtglYhxx.ZJHM, zjhm);
			}
			if(StringUtils.isNoEmpty(gj)){
				yhxx.add(MbXtglYhxx.GJ, gj);
			}
			if(StringUtils.isNoEmpty(xb)){
				yhxx.add(MbXtglYhxx.XB, xb);
			}
			if(StringUtils.isNoEmpty(mz)){
				yhxx.add(MbXtglYhxx.MZ, mz);
			}
			if(StringUtils.isNoEmpty(csrq)){
				yhxx.add(MbXtglYhxx.CSRQ, csrq);
			}
			if(StringUtils.isNoEmpty(dzyj)){
				yhxx.add(MbXtglYhxx.DZYJ, dzyj);
			}
			if(StringUtils.isNoEmpty(dh)){
				yhxx.add(MbXtglYhxx.DH, dh);
			}
			if(StringUtils.isNoEmpty(sorted)){
				yhxx.add(MbXtglYhxx.SORTED, sorted);
			}
			yhxx.add(MbXtglYhxx.SFWZJ, 0);
			yhxx.add(MbXtglYhxx.YHMM, md5.getMD5("123456"));
			if(dao.insert(yhxx)){
				int zdbh = yhxx.getIntValue(MbXtglYhxx.ZDBH);
				// 事务模式下，此标识将使此次事务提交
                LogUtils logUtils = new LogUtils();
                String desc = LogUtils.MbXtglYhxx_INSERT_DESC;
                logUtils.addLog(String.valueOf(LogUtils.type_insert), LogUtils.model_yhgl, contributor, desc);
				result.add(Status.SATUS,Status.OK);
				
			}else{
				throw new Exception("插入信息失败！");
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
			// 事务回滚，事务模式下，此标识将使此次事务回滚
			result.add(Status.SATUS, Status.FAILED);
		}
	    return result;
	}
	/**
	 * 人员详细信息查看
	 * @param request
	 * @param response
	 * @param data
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataById(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
			MbXtglYhxxDao dao = new MbXtglYhxxDao();
			Record yhxx = dao.getById(data.getIntValue("zdbh"));
			if(yhxx!=null){
				result.add(Status.ROWS, yhxx);
				result.add(Status.SATUS,Status.OK);
			}else{
				throw new Exception("查询信息失败！");
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		}
	    return result;
	}
	/**
	 * 修改人员信息
	 * @param request
	 * @param response
	 * @param data
	 * @return
	 */
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record update(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者
            
			MbXtglYhxxDao dao = new MbXtglYhxxDao();
			Record yhxx = dao.getById(data.getIntValue("zdbh"));
			if(yhxx!=null){
				String xm = data.getStrValue(MbXtglYhxx.XM);
				String szjg = data.getStrValue(MbXtglYhxx.SZJG);
				String zjlx = data.getStrValue(MbXtglYhxx.ZJLX);
				String zjhm = data.getStrValue(MbXtglYhxx.ZJHM);
				String gj = data.getStrValue(MbXtglYhxx.GJ);
				String xb = data.getStrValue(MbXtglYhxx.XB);
				String mz = data.getStrValue(MbXtglYhxx.MZ);
				String csrq = data.getStrValue(MbXtglYhxx.CSRQ);
				String dzyj = data.getStrValue(MbXtglYhxx.DZYJ);
				String dh = data.getStrValue(MbXtglYhxx.DH);
				String sorted = data.getStrValue(MbXtglYhxx.SORTED);
				if(StringUtils.isNoEmpty(xm)){
					yhxx.add(MbXtglYhxx.XM, xm);
				}
				if(StringUtils.isNoEmpty(szjg)){
					yhxx.add(MbXtglYhxx.SZJG, szjg);
				}
				if(StringUtils.isNoEmpty(zjlx)){
					yhxx.add(MbXtglYhxx.ZJLX, zjlx);
				}
				if(StringUtils.isNoEmpty(zjhm)){
					yhxx.add(MbXtglYhxx.ZJHM, zjhm);
				}
				if(StringUtils.isNoEmpty(gj)){
					yhxx.add(MbXtglYhxx.GJ, gj);
				}
				if(StringUtils.isNoEmpty(xb)){
					yhxx.add(MbXtglYhxx.XB, xb);
				}
				if(StringUtils.isNoEmpty(mz)){
					yhxx.add(MbXtglYhxx.MZ, mz);
				}
				if(StringUtils.isNoEmpty(csrq)){
					yhxx.add(MbXtglYhxx.CSRQ, csrq);
				}
				if(StringUtils.isNoEmpty(dzyj)){
					yhxx.add(MbXtglYhxx.DZYJ, dzyj);
				}
				if(StringUtils.isNoEmpty(dh)){
					yhxx.add(MbXtglYhxx.DH, dh);
				}
				if(StringUtils.isNoEmpty(sorted)){
					yhxx.add(MbXtglYhxx.SORTED, sorted);
				}
				if(dao.update(yhxx)){
					//int zdbh = yhxx.getIntValue(MbXtglYhxx.ZDBH);
					// 事务模式下，此标识将使此次事务提交
	                LogUtils logUtils = new LogUtils();
	                String desc = LogUtils.MbXtglYhxx_UPDATE_DESC;
	                logUtils.addLog(String.valueOf(LogUtils.type_update), LogUtils.model_yhgl, contributor, desc);
					result.add(Status.SATUS,Status.OK);
				}else{
					throw new Exception("更新信息失败！");
				}
			}else{
				throw new Exception("更新信息失败！");
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		}
	    return result;
	}
	/**
	 * 批量添加人员 
	 * @param request
	 * @param response
	 * @param path  路径
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record insertAdd(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		MD5 md5 = new MD5();
		MultipartFile file = new MultipartFile();
		MbXtglYhxxDao dao = new MbXtglYhxxDao();
		List<Record> record = new ArrayList<Record>();
		List<List<Object[]>> readExcel;
		try {
			readExcel = POIUtil.readExcel(file.RecvFile(request).getStrValue("savePath"));//读取excel,并获得解析数据
			for (List<Object[]> list : readExcel) {
				for (Object[] objects : list) {
					Record re = new Record();
					//排序序号
					re.add(MbXtglYhxx.SORTED, objects[0]);
					//所在机构
					Record mbXtglJg = new Record();
					mbXtglJg.add(MbXtglJg.JGMC, objects[1]);
					MbXtglJgDao mbXtglJgDao = new MbXtglJgDao();
					List<Record> mbXtglJgList = mbXtglJgDao.getListByProperty(mbXtglJg);
					if(mbXtglJgList.size()!=0){
						int mbXtglJgZdbh = mbXtglJgList.get(0).getIntValue("zdbh");
						re.add(MbXtglYhxx.SZJG, mbXtglJgZdbh);
					}else{
						re.add(MbXtglYhxx.SZJG, 1);
					}
					//姓名
					re.add(MbXtglYhxx.XM, objects[2]);
					//证件类型
					Record mbGyZjlx = new Record();
					mbGyZjlx.add(MbGyZjlx.NAME, objects[3]);
					MbGyZjlxDao mbGyZjlxDao = new MbGyZjlxDao();
					List<Record> mbGyZjlxList = mbGyZjlxDao.getListByProperty(mbGyZjlx);
					if(mbGyZjlxList.size()!=0){
						int mbGyZjlxId = mbGyZjlxList.get(0).getIntValue("id");
						re.add(MbXtglYhxx.ZJLX, mbGyZjlxId);
					}else{
						re.add(MbXtglYhxx.ZJLX, 1);
					}
					//证件号码
					re.add(MbXtglYhxx.ZJHM, objects[4]);
					//唯一编码
					re.add(MbXtglYhxx.WYBM, objects[5]);
					//性别
					if("男".equals(objects[6])){
						re.add(MbXtglYhxx.XB, 1);
					}
					else if("女".equals(objects[6])){
						re.add(MbXtglYhxx.XB, 2);
					}else{
						re.add(MbXtglYhxx.XB, 1);
					}	
					//国籍
					Record mbGyGj = new Record();
					mbGyGj.add(MbGyGj.NAME, objects[7]);
					MbGyGjDao mbGyGjDao = new MbGyGjDao();
					List<Record> mbGyGjList = mbGyGjDao.getListByProperty(mbGyGj);
					if(mbGyGjList.size()!=0){
						int mbGyGjId = mbGyGjList.get(0).getIntValue("id");
						re.add(MbXtglYhxx.GJ, mbGyGjId);
					}else{
						re.add(MbXtglYhxx.GJ, 1);
					}
					//民族
					Record paramer = new Record();
					paramer.add(MbGyZq.NAME, objects[8]);
					MbGyZqDao mbGyZqDao =new MbGyZqDao();
					List<Record> mbGyZqlist = mbGyZqDao.getListByProperty(paramer);
					if(mbGyZqlist.size()!=0){
						int mbGyZqid = mbGyZqlist.get(0).getIntValue("id");
						re.add(MbXtglYhxx.MZ, mbGyZqid);
					}else{
						re.add(MbXtglYhxx.MZ, 1);
					}
					//出生日期
					re.add(MbXtglYhxx.CSRQ, objects[9]);
					//电话
					re.add(MbXtglYhxx.DH, objects[10]);
					//电子邮件
					re.add(MbXtglYhxx.DZYJ, objects[11]);
					//是否为专家  默认为0
					re.add(MbXtglYhxx.SFWZJ, 0);
					//用户密码  默认为123456
					re.add(MbXtglYhxx.YHMM, md5.getMD5("123456"));
					record.add(re);
				}
				record.remove(0);//移除表头
				if(dao.insert(record)){
					result.add(Status.SATUS,Status.OK);
				}else{
					throw new Exception("添加信息失败！");
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		}
		return result;
	}
	/**
	 * 下载批量添加模板  
	 * @param request
	 * @param response
	 * @param 
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataXls(HttpServletRequest request, HttpServletResponse response,Record data) {
		Record result = new Record();
		try {
			OutputStream out = response.getOutputStream();
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
			String title = "用户信息";
			String filename = URLEncoder.encode(title, "UTF-8")+"_"+dfmt.format(new Date())+".xls";
			response.setHeader("Content-disposition", "attachment;filename="+filename);
	        HSSFWorkbook wb = new HSSFWorkbook();
	        //列表标题
	        HSSFSheet sheet = wb.createSheet(title);
	        HSSFSheet hs0 = wb.getSheetAt(0);
	        HSSFRow row = null;
	        HSSFCell cell = null;
	        row = hs0.createRow(0);
	        cell = row.createCell(0);
	    	cell.setCellValue("排序序号");
	    	cell = row.createCell(1);
	    	cell.setCellValue("所在机构");
	    	cell = row.createCell(2);
	    	cell.setCellValue("姓名");
	    	cell = row.createCell(3);
	    	cell.setCellValue("证件类型");
	    	cell = row.createCell(4);
	    	cell.setCellValue("证件号码");
	    	cell = row.createCell(5);
	    	cell.setCellValue("唯一编码");
	    	cell = row.createCell(6);
	    	cell.setCellValue("性别");
	    	cell = row.createCell(7);
	    	cell.setCellValue("国籍");
	    	cell = row.createCell(8);
	    	cell.setCellValue("民族");
	    	cell = row.createCell(9);
	    	cell.setCellValue("出生日期");
	    	cell = row.createCell(10);
	    	cell.setCellValue("电话");
	    	cell = row.createCell(11);
	    	cell.setCellValue("电子邮件");	
	    	//让列宽随着导出的列长自动适应
           for (int colNum = 0; colNum < 12; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }
	        wb.write(out);
	        out.close();
	        out.close();
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"导出失败！");
		}
		return result;
	}
	
	/**
	 * 上传批量添加模板
	 * @param request
	 * @param response
	 * @param 
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record updateDataXls(HttpServletRequest request, HttpServletResponse response,Record data) {
		Record result = new Record();
		try {
			
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"导出失败！");
		}
		return result;
	}
	
	/**
	 * 下载要离线批量修改的数据
	 * @param request
	 * @param response
	 * @param 
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record upDateXls(HttpServletRequest request, HttpServletResponse response,Record data) {
		Record result = new Record();
		try {
			OutputStream out = response.getOutputStream();
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
			String title = "用户信息";
			String filename = URLEncoder.encode(title, "UTF-8")+"_"+dfmt.format(new Date())+".xls";
			response.setHeader("Content-disposition", "attachment;filename="+filename);
	        HSSFWorkbook wb = new HSSFWorkbook();
	        //列表标题
	        HSSFSheet sheet = wb.createSheet(title);
	        HSSFSheet hs0 = wb.getSheetAt(0);
	        //隐藏自动编号
	        sheet.setColumnHidden(12, true);
	        HSSFRow row = null;
	        HSSFCell cell = null;
	        row = hs0.createRow(0);
	        cell = row.createCell(0);
	    	cell.setCellValue("排序序号");
	    	cell = row.createCell(1);
	    	cell.setCellValue("所在机构");
	    	cell = row.createCell(2);
	    	cell.setCellValue("姓名");
	    	cell = row.createCell(3);
	    	cell.setCellValue("证件类型");
	    	cell = row.createCell(4);
	    	cell.setCellValue("证件号码");
	    	cell = row.createCell(5);
	    	cell.setCellValue("唯一编码");
	    	cell = row.createCell(6);
	    	cell.setCellValue("性别");
	    	cell = row.createCell(7);
	    	cell.setCellValue("国籍");
	    	cell = row.createCell(8);
	    	cell.setCellValue("民族");
	    	cell = row.createCell(9);
	    	cell.setCellValue("出生日期");
	    	cell = row.createCell(10);
	    	cell.setCellValue("电话");
	    	cell = row.createCell(11);
	    	cell.setCellValue("电子邮件");
	    	cell = row.createCell(12);
	    	cell.setCellValue("自动编号");	
	    	if (listAll != null && listAll.size() > 0) {
                int num = 1;
                for (int i = 0; i < listAll.size(); i++) {
                    Record record=listAll.get(i);
                    Row row1 = hs0.createRow(num++);
                    //排序序号
                    row1.createCell(0).setCellValue(record.getStrValue(MbXtglYhxx.SORTED));
                    //机构名称
                    row1.createCell(1).setCellValue(record.getStrValue("jgmc"));
                    //姓名
                    row1.createCell(2).setCellValue(record.getStrValue(MbXtglYhxx.XM));
                    //证件类型
                    row1.createCell(3).setCellValue(record.getStrValue("zjlxName"));
                    //证件号码
                    row1.createCell(4).setCellValue(record.getStrValue(MbXtglYhxx.ZJHM));
                    //唯一编码
                    row1.createCell(5).setCellValue(record.getStrValue(MbXtglYhxx.WYBM));
                    //性别
                    if("1".equals(record.getStrValue(MbXtglYhxx.XB))){
                    	row1.createCell(6).setCellValue("男");
                    }else if("2".equals(record.getStrValue(MbXtglYhxx.XB))){
                    	row1.createCell(6).setCellValue("女");
                    }else{
                    	row1.createCell(6).setCellValue(record.getStrValue(MbXtglYhxx.XB));
                    }
                    //国籍
                    row1.createCell(7).setCellValue(record.getStrValue("gjName"));
                    //民族
                    row1.createCell(8).setCellValue(record.getStrValue("zqName"));
                    //出生日期
                    row1.createCell(9).setCellValue(record.getStrValue(MbXtglYhxx.CSRQ));
                    //电话
                    row1.createCell(10).setCellValue(record.getStrValue(MbXtglYhxx.DH));
                    //电子邮件
                    row1.createCell(11).setCellValue(record.getStrValue(MbXtglYhxx.DZYJ));
                    //自动编码   隐藏该列
                    row1.createCell(12).setCellValue(record.getStrValue(MbXtglYhxx.ZDBH));
                }
	        }
	        listAll.clear();
	    	//让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < 13; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }
	        wb.write(out);
	        out.close();
	        out.close();
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"导出失败！");
		}
		return result;
	}
	
	
	/**
	 * 离线批量修改人员  可修改的字段为排序序号、性别、国籍、民族
	 * @param request
	 * @param response
	 * @param path
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record updateAdd(HttpServletRequest request, HttpServletResponse response, Record data) {
		MultipartFile file = new MultipartFile();
		Record result = new Record();
		MbXtglYhxxDao dao = new MbXtglYhxxDao();
		List<Record> record = new ArrayList<Record>();
		List<List<Object[]>> readExcel;
		try {
			readExcel = POIUtil.readExcel(file.RecvFile(request).getStrValue("savePath"));//读取excel,并获得解析数据
			for (List<Object[]> list : readExcel) {
				for (Object[] objects : list) {
					Record re = new Record();
					re.add(MbXtglYhxx.SORTED, objects[0]);//排序序号
					re.add(MbXtglYhxx.XB, objects[6]);//性别
					re.add(MbXtglYhxx.GJ, objects[7]);//国籍
					re.add(MbXtglYhxx.MZ, objects[8]);//民族
					re.add(MbXtglYhxx.ZDBH, objects[12]);//排序序号
					record.add(re);
				}
				record.remove(0);//移除表头
				List<Record> record1 = new ArrayList<Record>();
				for (Record res : record) {
					Record yhxx = dao.getById(res.getIntValue("zdbh"));
					if(yhxx!=null){
						//排序序号
						yhxx.add(MbXtglYhxx.SORTED,res.getStrValue(MbXtglYhxx.SORTED));
						//男女
						if("男".equals(res.getStrValue(MbXtglYhxx.XB))){
							yhxx.add(MbXtglYhxx.XB, 1);
						}else if("女".equals(res.getStrValue(MbXtglYhxx.XB))){
							yhxx.add(MbXtglYhxx.XB, 2);
						}else{
							yhxx.add(MbXtglYhxx.XB, res.getStrValue(MbXtglYhxx.XB));
						}
						//国籍
						Record mbGyGj = new Record();
						mbGyGj.add(MbGyGj.NAME, res.getStrValue(MbXtglYhxx.GJ));
						MbGyGjDao mbGyGjDao = new MbGyGjDao();
						List<Record> mbGyGjList = mbGyGjDao.getListByProperty(mbGyGj);
						if(mbGyGjList.size()!=0){
							int mbGyGjId = mbGyGjList.get(0).getIntValue("id");
							yhxx.add(MbXtglYhxx.GJ, mbGyGjId);
						}else{
							yhxx.add(MbXtglYhxx.GJ, res.getStrValue(MbXtglYhxx.GJ));
						}
						//民族
						Record mbGyZq = new Record();
						mbGyZq.add(MbGyZq.NAME, res.getStrValue(MbXtglYhxx.MZ));
						MbGyZqDao mbGyZqDao =new MbGyZqDao();
						List<Record> mbGyZqList = mbGyZqDao.getListByProperty(mbGyZq);
						if(mbGyZqList.size()!=0){
							int mbGyZqId = mbGyZqList.get(0).getIntValue("id");
							yhxx.add(MbXtglYhxx.MZ, mbGyZqId);
						}else{
							yhxx.add(MbXtglYhxx.MZ, res.getStrValue(MbXtglYhxx.MZ));
						}
						record1.add(yhxx);
					}
				}
				if(dao.update(record1)){
					result.add(Status.SATUS,Status.OK);
				}else{
					throw new Exception("修改信息失败！");
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		}
		return result;
	}
	/**
	 * 在线批量修改人员信息  
	 * @param request
	 * @param response
	 * @param data  需要传递需要修改人的自动编号，多个的中间以逗号隔开
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record updateBatch(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
			String strValue = data.getStrValue(MbXtglYhxx.ZDBH);
			String sorted = data.getStrValue(MbXtglYhxx.SORTED);
			String gj = data.getStrValue(MbXtglYhxx.GJ);
			String xb = data.getStrValue(MbXtglYhxx.XB);
			String mz = data.getStrValue(MbXtglYhxx.MZ);
			StringBuilder sb =new StringBuilder();
			sb.append("update ").append(MbXtglYhxx.MB_XTGL_YHXX_TABLE_NAME);
			if(StringUtils.isNoEmpty(sorted)||StringUtils.isNoEmpty(gj)//判断是否有更新字段
					||StringUtils.isNoEmpty(xb)||StringUtils.isNoEmpty(mz) ){
				sb.append(" set");
			}else{
				result.add(Status.SATUS,Status.OK);//没有更新的字段
			}
			if(StringUtils.isNoEmpty(sorted)){
				if(StringUtils.isNoEmpty(gj)||StringUtils.isNoEmpty(xb)||StringUtils.isNoEmpty(mz)){
					sb.append(" sorted =").append(sorted).append(",");
				}else{
					sb.append(" sorted =").append(sorted);
				}
			}
			if(StringUtils.isNoEmpty(gj)){
				if(StringUtils.isNoEmpty(xb)||StringUtils.isNoEmpty(mz)){
					sb.append(" gj = '"+gj+"' ").append(",");
				}else{
					sb.append(" gj = '"+gj+"' ");
				}
			}
			if(StringUtils.isNoEmpty(xb)){
				if(StringUtils.isNoEmpty(mz)){
					sb.append(" xb = '"+xb+"' ").append(",");
				}else{
					sb.append(" xb = '"+xb+"' ");
				}
			}
			if(StringUtils.isNoEmpty(mz)){
				sb.append(" mz = '"+mz+"' ");
			}
			if(StringUtils.isNoEmpty(strValue)){
				sb.append(" where zdbh in(").append(strValue).append(")");
			}
			IDBActor actor = DBActorMgr.getDBActor("cnmuseum");
			actor.ExeSql(sb.toString());
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		} 
	    return result;
	}
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record delete(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者
            
			int zdbh = data.getIntValue(MbXtglYhxx.ZDBH);
			MbXtglYhxxDao dao = new MbXtglYhxxDao();
			Record obj = dao.getById(zdbh);
			if(obj!=null){
				obj.add(MbXtglYhxx.ZHUXIAO, 1);
				dao.update(obj);
				
                LogUtils logUtils = new LogUtils();
                String desc = LogUtils.MbXtglYhxx_DELETE_DESC;
                logUtils.addLog(String.valueOf(LogUtils.type_delete), LogUtils.model_yhgl, contributor, desc);
			}
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		} 
	    return result;
	}
	
	/**
	 * 获取专家下拉列表展示
	 */

	@rquestMethod(MethodNote = ReqMode.All)
	public Record getZjPullData(HttpServletRequest request, HttpServletResponse response, Record data){
		Record result = new Record();
		try { 
			MbXtglYhxxDao dao = new MbXtglYhxxDao();
			
		    String sql = "SELECT * from mb_xtgl_yhxx where 1=1"+" and sfwzj= 1";
		    String ordersql = " order by zdbh asc ";	
			List<Record> list = dao.getBySql(sql+ordersql);
			// 组装数据
			result.add(Status.ROWS, list);
			// 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
		    logger.error(e.getMessage());
		    result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息
			result.add(Status.MSG,"查询失败！");
		}
	    return result;
	}
	
	
	
}